Hi,

I have a question on database auditing on SELECT statements on a table.

syntax:

CREATE DATABASE AUDIT SPECIFICATION AuditSelectSpec FOR SERVER AUDIT Audit_Select_Host ADD (SELECT ON OBJECT::[STVDB].[HOST] BY [dbo])

When I query the fn(), I also see INSERT/UPDATE/DELETE statements, but I wanted to audit only SELECTs, and my syntax is clearly showing I'm auditing only SELECT. I'm not sure what mistake I'm doing or what's the mistake I'm doing.

Could you please help me, I need some hand?

Version of SQL server: sql server 2012

Another question related to auditing.

How could I get SELECT statement start and end times(to calculate execution time of the query) using database auditing in sqlserver?

Regards

Raju  angani@gmail.com

The AdventureWorks sample at CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) works even when you specify only SELECT as audit action.

The only thing I can see going wrong is: Do you use the correct audit file name? In my case the audit can be read by using

SELECT  *
FROM    sys.fn_get_audit_file('C:\Program Files\Microsoft\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Payrole_Security_Audit_4256C1F0-3817-444D-9E9F-1372E60A877D_0_130251772943580000.sqlaudit',
                              DEFAULT, DEFAULT);

You'll get the audit file name with

SELECT  name ,
        audit_file_path
FROM    sys.dm_server_audit_status;

• Edited by Stefan HoffmannMVP 1 hour 30 minutes ago

The AdventureWorks sample at CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL) works even when you specify only SELECT as audit action.

The only thing I can see going wrong is: Do you use the correct audit file name? In my case the audit can be read by using

SELECT  *
FROM    sys.fn_get_audit_file('C:\Program Files\Microsoft\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Payrole_Security_Audit_4256C1F0-3817-444D-9E9F-1372E60A877D_0_130251772943580000.sqlaudit',
                              DEFAULT, DEFAULT);

You'll get the audit file name with

SELECT  name ,
        audit_file_path
FROM    sys.dm_server_audit_status;

• Edited by Stefan HoffmannMVP Wednesday, October 02, 2013 8:57 AM
• Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Friday, October 04, 2013 1:46 AM
• Marked as answer by Sofiya LiMicrosoft contingent staff, Moderator Wednesday, October 09, 2013 9:47 AM

Hi Raju ,

Try like this , you might have enabled audit for Insert/Update/Delete also , Kindly check it out .

You can consider using SQL Server profiler and SQL Server traces (besides manual auditing), but it could get very complex and difficult.

There are some third party tools like SQL Compliance manager from Idera, and SQL auditing and compliance tool from ApexSQL. These are able to audit exact SQL statements that have been executed, including SELECTs.